<?php

namespace app\expertReport\logic;

use app\expertReport\model\ExpertVideo as ExpertVideoModel;
use think\facade\Db;
use app\data\util\HttpUtil;
use ba\Exception;
use think\facade\Config;
use think\facade\Cache;
use app\common\library\Excel;

class ExpertVideoLogic
{
    protected $model = null;

    public function __construct()
    {
        $this->model = new ExpertVideoModel();
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function index($params = [])
    {
        $stat_time_day = $params['stat_time_day'] ?? '';
        $limit = $params['limit'] ?? 10;
        
        $order = $params['order'] ?? 'id,desc';

        $where = [];
        if (!empty($stat_time_day)) {
            $createtimeArr = explode(',', $stat_time_day);
            $where[] = ['stat_time_day', '>=', strtotime($createtimeArr[0])];
            $where[] = ['stat_time_day', '<=', strtotime($createtimeArr[1])];
            $display_day = strtotime($createtimeArr[1]);
        }else{
            $where[] = ['stat_time_day', '=', strtotime(date('Y-m-d',time() - 16 * 3600))];
            $display_day = strtotime(date('Y-m-d',time() - 16 * 3600));
        }
        
        $order = $params['order'] ?? 'id,desc';

        //排序
        list($field, $direction) = explode(',', $order);  
        $field = trim($field);  
        $direction = trim($direction);  
        $order = [$field => $direction];  
        
        $list = $this->model
            ->field('name,sum(conversion) as conversion,sum(spend) as spend,sum(total_onsite_shopping_value) as total_onsite_shopping_value')
            ->where($where)
            ->group("name")
            ->order($order)
            ->paginate($limit);
        $lists = $list->items();

        foreach ($lists as $k=>$v) {
            if($v['total_onsite_shopping_value'] > 0.001 && $v['spend'] > 0.001){
                $lists[$k]['onsite_shopping_roas'] = number_format($v['total_onsite_shopping_value'] / $v['spend'], 2, '.', '');
            }else{
                $lists[$k]['onsite_shopping_roas'] = 0;
            }
            $lists[$k]['stat_time_day'] = $display_day;
        }
        
        
        $sum = $this->model
            ->field('sum(spend) as spend,sum(total_onsite_shopping_value) as total_onsite_shopping_value,sum(conversion) as conversion,AVG(onsite_shopping_roas) as onsite_shopping_roas')
            ->where($where)
            ->order('id desc')
            ->find();
        if($sum['spend'] > 0.001 && $sum['total_onsite_shopping_value'] > 0.001){
            $onsite_shopping_roas = number_format($sum['total_onsite_shopping_value'] / $sum['spend'], 2, '.', '');
        }else{
            $onsite_shopping_roas = 0;
        }
        
        $data = [
            'list' => $lists,
            'total' => $list->total(),
            'spend' => !empty($sum['spend']) ? number_format($sum['spend'], 2, '.', '') : 0,
            'total_onsite_shopping_value' => !empty($sum['total_onsite_shopping_value']) ? number_format($sum['total_onsite_shopping_value'], 2, '.', '') : 0,
            'conversion' => !empty($sum['conversion']) ? number_format($sum['conversion'], 2, '.', '') : 0,
            'onsite_shopping_roas' => $onsite_shopping_roas,
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export($params = [])
    {
        $stat_time_day = $params['stat_time_day'] ?? '';
        
        $order = $params['order'] ?? 'id,desc';

        $where = [];
        if (!empty($stat_time_day)) {
            $createtimeArr = explode(',', $stat_time_day);
            $where[] = ['stat_time_day', '>=', strtotime($createtimeArr[0])];
            $where[] = ['stat_time_day', '<=', strtotime($createtimeArr[1])];
            $display_day = strtotime($createtimeArr[1]);
        }else{
            $where[] = ['stat_time_day', '=', strtotime(date('Y-m-d',time() - 16 * 3600))];
            $display_day = strtotime(date('Y-m-d',time() - 16 * 3600));
        }
        
        $order = $params['order'] ?? 'id,desc';

        //排序
        list($field, $direction) = explode(',', $order);  
        $field = trim($field);  
        $direction = trim($direction);  
        $order = [$field => $direction];  
        
        $lists = $this->model
            ->field('name,sum(conversion) as conversion,sum(spend) as spend,sum(total_onsite_shopping_value) as total_onsite_shopping_value')
            ->where($where)
            ->group("name")
            ->order($order)
            ->select()
            ->toarray();

        foreach ($lists as $k=>$v) {
            $lists[$k]['onsite_shopping_roas'] = number_format($v['total_onsite_shopping_value'] / $v['spend'], 2, '.', '');
            $lists[$k]['stat_time_day'] = date('Y-m-d',$display_day);
        }
        
        $header[] = ['姓名', 'name'];
        $header[] = ['花费', 'spend'];
        $header[] = ['收入', 'total_onsite_shopping_value'];
        $header[] = ['订单', 'conversion'];
        $header[] = ['ROI', 'onsite_shopping_roas'];
        $header[] = ['时间', 'stat_time_day'];
        $fileName = "export_advaccount" . time();
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function info($params = [])
    {
        $stat_time_day = $params['stat_time_day'] ?? '';
        $name = $params['name'] ?? '';
        $limit = $params['limit'] ?? 10;
        $where = [];
        //总消耗不为0
        // $where[] = ['a.spend', '>=', 0.001];

        if (!empty($stat_time_day)) {
            $createtimeArr = explode(',', $stat_time_day);
            $where[] = ['a.stat_time', '>=', strtotime($createtimeArr[0])];
            $where[] = ['a.stat_time', '<=', strtotime($createtimeArr[1])];
        }

        if (!empty($name)) {
            $config = Config::get('Logisticskey.' . 'ExpertVideo');
            foreach ($config as $campaign) {
                if (strpos($name, $campaign[0]) !== false) {
                    $campaignKey = $campaign[1];
                    break;
                }
            }
        }
        if (isset($campaignKey)) {
            $where[] = ['a.ad_name', 'like', '%' . $campaignKey . '%'];
        }

        $list = Db::name('advertising_campaign')
            ->field('a.*,b.advertiser_name')
            ->alias('a')
            ->join('ba_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->where($where)
            ->order('a.stat_time desc,a.spend desc')
            ->paginate($limit);
            
        $lists = $list->items();

        $data = [
            'list' => $lists,
            'total' => $list->total(),
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

}
